with  tmp_tab_sging as (
       select
         t.waybill_no
        ,nvl(t.scan_site_code,t1.site_code)  as scan_site_code
        ,t.signed_type signed_type
        ,t.obj_waybill_no obj_waybill_no
        ,t.str 
        ,t.dt dt
        ,t.type type
        ,case when t.type=1 then 1 else 0 end as type1
        ,case when t.type=1 and t.writeback_flag =0  then 1 else 0 end as writeback_flag
        ,case when t.type=1 and t.p_waybill_no is not null then 1 else 0 end as p_waybill_no
        ,t.scan_user scan_user
        ,t.scan_user_code scan_user_code
        ,t.pistol_id pistol_id
        ,t1.ordersource_code ordersource_code
        ,t1.ordersource_name ordersource_name
        ,t1.dispatch_staff_code dispatch_staff_code
        ,t1.dispatch_staff_name dispatch_staff_name
        ,nvl(t1.is_reback,0) is_reback
        ,t.store_code store_code
        ,t.store_name store_name
        ,t.business_name
        ,t.source
        ,row_number()over(partition by t.waybill_no order by t.type desc) as rn

    from
        (
            select * from (
                        select
                            t.waybill_no,
                            row_number()over(partition by t.waybill_no,scan_site_code order by t.scan_time desc) as rn,
                            t.scan_site_code,
                            t.scan_time ,
                            to_date(t.scan_time) as dt,
                            null as signed_type,
                            null as obj_waybill_no,
                            null as str,
                            1 as type,
                            t.writeback_flag as writeback_flag,
                            t1.waybill_no as p_waybill_no ,
                            scan_user as  scan_user ,
                            scan_user_code as  scan_user_code ,
                            case when t.real_oper_source_code ='94' and t.writeback_flag=0 then 1
                                 when t.real_oper_source_code ='91' and t.writeback_flag=0 then 2
                                 when t.real_oper_source_code ='61' and t.writeback_flag=0 then 4
                                 when t.real_oper_source_code ='97' and t.writeback_flag=0 then 5
                                 when t.writeback_flag=0 then  3 else 999 end  pistol_id,
                            post_station_code as store_code,
                            storage_address  as store_name,
                            t2.business_name,
                            t.source

                        from
                            jms_dwd.dwd_tab_barscan_collect_base_dt t
                           left join ( select
                                        t.waybill_id as waybill_no
                                       from  jms_dwd.dwd_oms_order_performance_main_base_dt t
                                       where t.dt>='{{ execution_date | date_add(-6) | cst_ds }}' and t.dt<='{{ execution_date | cst_ds }}'  and  is_contact=1

                                       group by t.waybill_id
                                     ) t1 on t.waybill_no=t1.waybill_no
                           left join (
                                      select business_id, max(business_name) as business_name from jms_dim.dim_tab_terminal_secret_base group by business_id
                                      ) t2 on t.real_oper_source_code=t2.business_id
                        where
                            t.dt >= '{{ execution_date | date_add(-6) | cst_ds }}' and dt <= '{{ execution_date | cst_ds }}'
                            and scan_type = '出库扫描'
                            and to_date(t.scan_time)='{{ execution_date | cst_ds }}'

                ) t where t.rn =1

                union all
                select * from (
                            select
                                t.waybill_no,
                                row_number()over(partition by t.waybill_no,signed_site_code order by t.signed_time desc) as rn,
                                t.signed_site_code as scan_site_code,
                                t.signed_time as scan_time,
                                to_date(t.signed_time) as dt,
                                t.signed_type,
                                t3.waybill_no as obj_waybill_no,
                                ttt.labelType as str,
                                2 as type,
                                null as writeback_flag,
                                null as p_waybill_no,
                                t.deliver_user as scan_user ,
                                t.send_user_code as scan_user_code ,
                                null as pistol_id,
                                null as store_code,
                                null  as store_name,
                                null as business_name,
                                t.source
                            from
                                jms_dwd.dwd_tab_barscan_sign_base_dt t
                            left join (select * from (
                                                select
                                                  row_number()over(partition by t.waybillid  order by nvl(t.event_time,'1999-01-01 00:00:00') desc ) as rn
                                                ,t.waybillid
                                                ,t.labelType
                                                from jms_dwd.dwd_tab_barscan_sign_realtime_flink_dt  t
                                                where  t.dt>= date_add('{{ execution_date | cst_ds }}', -7) and t.dt<= '{{ execution_date | cst_ds }}'
                                                ) t where rn=1) ttt on ttt.waybillid=t.waybill_no
                            left join (
                                select t.waybill_no from (
                                    select
                                      t.waybill_no
                                    from jms_dwd.dwd_work_order_dt t
                                    where dt between date_add('{{ execution_date | cst_ds }}', -45) and '{{ execution_date | cst_ds }}'
                                    and first_type_code=21
                                    and second_type_code=2104
                                    union all
                                    select
                                      t.waybill_no
                                    from jms_dwd.dwd_project_work_order_dt t
                                     where dt between date_add('{{ execution_date | cst_ds }}', -45) and '{{ execution_date | cst_ds }}'  and t.work_order_source_id=1
                                ) t group by t.waybill_no
                            ) t3
                            on t.waybill_no = t3.waybill_no
                            where
                                t.dt >= '{{ execution_date | date_add(-6) | cst_ds }}' and dt <= '{{ execution_date | cst_ds }}'
                                and to_date(t.signed_time)='{{ execution_date | cst_ds }}'

                           ) t where t.rn =1
         ) t
    left join ( select t.waybill_no
                     ,max(order_source_code_order) ordersource_code
                     ,max(case when order_source_name_order ='拼多多' then '桃花岛'
                               when order_source_name_order ='淘宝'   then '七星潭'
                               when order_source_name_order ='快手'   then '逍遥峰'
                               when order_source_name_order ='抖音'   then '紫金山'
                               when order_source_name_order ='京东'   then '极地湾'
                                else order_source_name_order end
                               ) ordersource_name
                    ,max(coalesce(t.sign_later_scansitecode,t.deliver_later_scansitecode,t1.send_station_network_code)) as site_code
                    ,max(dispatch_staff_code) as dispatch_staff_code
                    ,max(dispatch_staff_name) as dispatch_staff_name
                     ,max(is_reback) is_reback
                    from jms_dwd.dwd_wide_unsign_summary_waybill_dt t
                    left join ( select * from ( select
                                                    t.waybill_no,
                                                    t.scan_site_code as send_station_network_code,
                                                    t.scan_time,
                                                    row_number()over(partition by t.waybill_no order by t.scan_time desc ) as rn
                                                from jms_dwd.dwd_tab_barscan_arrival_base_dt t
                                                 where t.dt>='{{ execution_date | date_add(-30) | cst_ds }}'   and t.dt<='{{ execution_date  | cst_ds }}'  and t.network_type='4'
                                ) t where t.rn =1
                             )  t1 on t.waybill_no=t1.waybill_no
                    where t.dt>='{{ execution_date | date_add(-30) | cst_ds }}'  and t.dt<='{{ execution_date | cst_ds }}'
                    group by t.waybill_no
               ) t1 on t1.waybill_no = t.waybill_no


)


insert overwrite table jms_dm.dm_door_sign_rate_detail_dt partition ( dt )
select
     t.dt as date_time
    ,t.waybill_no
    ,tt.manage_code
    ,tt.manage_name
    ,tt.provider_id
    ,tt.provider_desc
    ,tt.agent_code
    ,tt.agent_name
    ,tt.fran_code
    ,tt.fran_name
    ,tt.name as scan_site_name
    ,t.scan_site_code as scan_site_code
    ,t.signed_type
    ,case when t.type=1 then 1 else 2 end as timely_type --1入库,2签收
    ,1 as is_timely
    ,case when (t.type=2 and t.str = '送货上门' ) or ( t.type=1 and t.writeback_flag = '1' )  then 1 else 0 end  as is_door --是否送货上门，1是，0否
    ,case when t.type1=1  then 1 else 0 end  as is_stage_sign --是否送入驿站，1是，0否
    ,case when t.type=2 and t.obj_waybill_no is not null then 1 else 0 end as is_xusign --是否虚假签收，1是，0否
    ,case when t.writeback_flag=1 then 1 else 0 end   as  is_writeback_flag --是否驿站上门票数，1是，0否
    ,case when t.p_waybill_no=1 then 1 else 0 end as is_performance_flag --是否标电联票数，1是，0否
    ,t.type1 as expand3
    ,null as expand4
    ,null as expand5
    ,nvl(t.scan_user, t.dispatch_staff_name) scan_user
    ,nvl(t.scan_user_code,t.dispatch_staff_code)  scan_user_code
    ,t.pistol_id
    ,t.ordersource_code
    ,t.ordersource_name
    ,t.is_reback
    ,t.store_code store_code   --新增字段
    ,t.store_name store_name   --新增字段
    ,case when t.str ='代收点' then '收派员代收点'
          when t.str ='送货上门' then '收派员上门'
          when t.type =1 then '驿站出库'
          else '收派员其他' end as sign_big --新增字段
    ,tt.city_id
    ,tt.city_desc
    ,tt.area_id
    ,tt.area_desc
    ,t.business_name
    ,t.source
    ,case when t2.waybill_no is not null   then 1 else 0 end as should_door
    ,case when tt1.code is not null  then 1 else 0 end as is_dk --是否DK仓库,1是0否
    ,t.dt
from tmp_tab_sging t
left join  jms_dim.dim_network_whole_massage tt on t.scan_site_code=tt.code
left join  jms_dim.dim_door_sign_rate_dk tt1 on t.scan_site_code=tt1.code

left join (
      select
          t.bill_code as waybill_no
    from
        jms_dwd.dwd_tab_barscan_transfer_mail_base_dt t
    where  t.dt>='{{ execution_date | date_add(-6) | cst_ds }}'  and t.dt<='{{ execution_date  | cst_ds }}'
    group  by t.bill_code
    ) t1 on t.waybill_no=t1.waybill_no

left join (
        select
          t.waybill_no as waybill_no
        from
            jms_dwd.dwd_tab_ops_intervene_input_dt t
        where  t.dt>='{{ execution_date | date_add(-7) | cst_ds }}'  and t.dt<='{{ execution_date  | cst_ds }}'
              and business_type='labelShsm'
        group  by waybill_no
    ) t2 on t.waybill_no=t2.waybill_no


where t.rn =1 and  t1.waybill_no is null
 distribute by pmod(hash(t.waybill_no), 120)
;
